package com.nju.crawler.repository.persist;

import com.nju.crawler.repository.PersistService;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.JdbcTransactionManager;
import org.springframework.stereotype.Service;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @authorliyunfei
 * @date2022/11/13
 **/
@Service
public class ExternalPersistServiceImpl implements PersistService {
    @Value("${spring.datasource.driver-class-name}")
    String classDriverName;
    @Value("${spring.datasource.url}")
    String jdbcUrl;
    @Value("${spring.datasource.username}")
    String userName;
    @Value("${spring.datasource.password}")
    String password;
    //JdbcTemplate jdbcTemplate = new JdbcTemplate();
    private final static String INSERT_SQL = "INSERT INTO repo_info VALUES ";
    private JdbcTemplate jdbcTemplate;
    private JdbcTransactionManager jdbcTransactionManager;
    // 需单独解耦进行配置
    @PostConstruct
    void init(){
        //DataSource source = new DriverManagerDataSource();
        HikariDataSource source = new HikariDataSource();
        source.setJdbcUrl(jdbcUrl);
        source.setDriverClassName(classDriverName);
        source.setUsername(userName);
        source.setPassword(password);
        source.setMaxLifetime(1200000);
        this.jdbcTemplate = new JdbcTemplate(source);
    }

    @Override
    public void batchInsert(List<String> values) {
        String sql = "INSERT INTO repo_info VALUES ";//(),()
        for(String value:values){
            sql = sql + "("+value+") ,"; // value user_name,repo_name
        }
        sql = sql.substring(0,sql.length()-2)+";";
        System.out.println("SQL::"+sql);
        //this.jdbcTemplate.
        this.jdbcTemplate.batchUpdate(sql);

    }

    @Override
    public void insert(String value) {
        String sql = INSERT_SQL;
        sql= sql +"(" + value + ")";
        // 事务的处理
        this.jdbcTemplate.update(sql);
    }

    @Override
    public List<Map<String,Object>> topListQuery(int limit) {
        String sql = "SELECT * FROM top_res ORDER BY id desc limit "+limit;
        List<Map<String,Object>> mapList =  this.jdbcTemplate.queryForList(sql);
        return mapList;
    }

    @Override
    public List<Map<String, Object>> languageListQuery(int limit) {
        String sql = "SELECT * FROM lang_res ORDER BY id desc limit "+limit;
        List<Map<String,Object>> mapList =  this.jdbcTemplate.queryForList(sql);
        return mapList;
    }

    // 公共复用、抽象
    @Override
    public List<Map<String, Object>> licenseListQuery(int limit) {
        String sql = "SELECT * FROM license_res ORDER BY id desc limit "+limit;
        List<Map<String,Object>> mapList =  this.jdbcTemplate.queryForList(sql);
        return mapList;
    }


    public void batchInsertByArgs(List<Object[]> args){
         String sql = "INSERT INTO repo_info(user_name,repo_name,language,description,fork_count,star_count,issue_count,size,license) VALUES(?,?,?,?,?,?,?,?,?)";
         this.jdbcTemplate.batchUpdate(sql,args);
    }

    public void batchInsertUserByArgs(List<Object[]> args){
        String sql = "INSERT INTO user(user_id,user_name,follows_id,create_time) VALUES(?,?,?,?)";
        this.jdbcTemplate.batchUpdate(sql,args);
    }

    public void batchInsertUserRepoByArgs(List<Object[]> args){
        String sql = "INSERT INTO user_repo(user_id,repo_name,type,create_time) VALUES(?,?,?,?)";
        this.jdbcTemplate.batchUpdate(sql,args);
    }

    @Override
    public List<Map<String, Object>> usersListQuery(int limit) {
        String sql = "SELECT * FROM user LIMIT 0,"+limit;
        return this.jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> userRepoListQuery(int limit) {
//        String sql = "SELECT * FROM user,user_repo WHERE user_repo.user_id<2000 " +
//                "AND user.user_id = user_repo.user_id";
        String sql = "SELECT * FROM user_repo WHERE user_id < "+limit;

        return this.jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> userActivityQuery(int limit) {
        String sql;
        if(limit>0)
          sql = "SELECT * FROM aus LIMIT 0,"+limit;
        else
          sql =  "SELECT * FROM aus";
        return this.jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> userCategoryQuery(int limit) {
        String sql;
        if(limit>0)
            sql = "SELECT * FROM cchs LIMIT 0,"+limit;
        else
            sql =  "SELECT * FROM cchs";
        return this.jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> repoPopularQuery(int limit) {
        String sql;
        if(limit>0)
            sql = "SELECT * FROM prs LIMIT 0,"+limit;
        else
            sql =  "SELECT * FROM prs";
        return this.jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> repoRecommendQuery(int limit) {
        String sql;
        if(limit>0)
            sql = "SELECT * FROM urr LIMIT 0,"+limit;
        else
            sql =  "SELECT * FROM urr";
        return this.jdbcTemplate.queryForList(sql);
    }
}
